package com.easyjava.builder;

import com.easyjava.bean.Constants;
import com.easyjava.bean.FieldInfo;
import com.easyjava.bean.TableInfo;
import com.easyjava.utils.JsonUtils;
import com.easyjava.utils.PropertiesUtils;
import com.easyjava.utils.StringUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @projectName: easyjava
 * @author: Li
 * @description: 读取数据库表结构
 */

public class BuildTable {

    private static final Logger logger = LoggerFactory.getLogger(BuildTable.class);
    private static Connection conn = null;

    private static String SQL_SHOW_TABLE_STATUS = "show table status";

    private static String SQL_SHOW_TABLE_FIELDS = "show full fields from %s";

    private static String SQL_SHOW_TABLE_INDEX = "show index from %s";

    static {
        String driverName = PropertiesUtils.getString("db.driver.name");
        String url = PropertiesUtils.getString("db.url");
        String username = PropertiesUtils.getString("db.username");
        String password = PropertiesUtils.getString("db.password");
        try {
            Class.forName(driverName);
            conn = java.sql.DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            logger.error("数据库连接失败", e);
        }
    }

    // 获取数据库表结构
    public static List<TableInfo> getTables() {
        PreparedStatement ps = null;
        ResultSet tableResult = null;

        List<TableInfo> tableInfoList = new ArrayList();
        try {
            ps = conn.prepareStatement(SQL_SHOW_TABLE_STATUS);
            tableResult = ps.executeQuery();
            while (tableResult.next()) {
                String tableName = tableResult.getString("Name");
                String comment = tableResult.getString("Comment");

                String beanName = tableName;
                if (Constants.IGNORE_TABLE_PREFIX) {
                    beanName = tableName.substring(beanName.indexOf("_") + 1);
                }
                beanName = processField(beanName, true);

                TableInfo tableInfo = new TableInfo();
                tableInfo.setTableName(tableName);
                tableInfo.setBeanName(beanName);
                tableInfo.setComment(comment);
                tableInfo.setBeanQueryName(beanName + Constants.SUFFIX_BEAN_QUERY);

                // 读取表字段信息
                readFieldInfo(tableInfo);
                // 读取表索引信息
                getKeyIndexInfo(tableInfo);

                tableInfoList.add(tableInfo);
            }
        } catch (Exception e) {
            logger.error("读取表失败", e);
        } finally {
            if (tableResult != null) {
                try {
                    tableResult.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return tableInfoList;
    }

    // 读取表字段信息
    private static void readFieldInfo(TableInfo tableInfo) {
        PreparedStatement ps = null;
        ResultSet fieldResult = null;

        Boolean haveDateTime = false;
        Boolean haveDate = false;
        Boolean haveBigDecimal = false;

        List<FieldInfo> fieldInfoList = new ArrayList();

        List<FieldInfo> fieldExtendList = new ArrayList();
        try {
            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_FIELDS, tableInfo.getTableName()));
            fieldResult = ps.executeQuery();
            while (fieldResult.next()) {
                String Field = fieldResult.getString("Field");
                String Type = fieldResult.getString("Type");
                String Extra = fieldResult.getString("Extra");
                String Comment = fieldResult.getString("Comment");

                if (Type.indexOf("(") > 0) {
                    Type = Type.substring(0, Type.indexOf("("));
                }
                String propertyName = processField(Field, false);

                FieldInfo fieldInfo = new FieldInfo();
                fieldInfoList.add(fieldInfo);

                fieldInfo.setFieldName(Field);
                fieldInfo.setSqlType(Type);
                fieldInfo.setIsAutoIncrement("auto_increment".equals(Extra));
                fieldInfo.setComment(Comment);
                fieldInfo.setPropertyName(propertyName);
                fieldInfo.setJavaType(processJavaType(Type));

                if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, Type)) {
                    haveDateTime = true;
                }
                if (ArrayUtils.contains(Constants.SQL_DATE_TYPES, Type)) {
                    haveDate = true;
                }
                if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES, Type)) {
                    haveBigDecimal = true;
                }
                if (ArrayUtils.contains(Constants.SQL_STRING_TYPES, fieldInfo.getSqlType())) {
                    FieldInfo fieldFuzzy = new FieldInfo();
                    fieldFuzzy.setPropertyName(propertyName + Constants.SUFFIX_BEAN_QUERY_FUZZY);
                    fieldFuzzy.setJavaType(fieldInfo.getJavaType());
                    fieldFuzzy.setFieldName(fieldInfo.getFieldName());
                    fieldFuzzy.setSqlType(fieldInfo.getSqlType());
                    fieldExtendList.add(fieldFuzzy);
                }
                if (ArrayUtils.contains(Constants.SQL_DATE_TYPES, fieldInfo.getSqlType()) || ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, fieldInfo.getSqlType())) {
                    String propertyStartName = fieldInfo.getPropertyName() + Constants.SUFFIX_BEAN_QUERY_TIME_START;
                    String propertyEndName = fieldInfo.getPropertyName() + Constants.SUFFIX_BEAN_QUERY_TIME_END;

                    FieldInfo timeStartField = new FieldInfo();
                    timeStartField.setPropertyName(propertyStartName);
                    timeStartField.setFieldName(fieldInfo.getFieldName());
                    timeStartField.setJavaType("String");
                    timeStartField.setSqlType(fieldInfo.getSqlType());
                    fieldExtendList.add(timeStartField);

                    FieldInfo timeEndField = new FieldInfo();
                    timeEndField.setPropertyName(propertyEndName);
                    timeEndField.setFieldName(fieldInfo.getFieldName());
                    timeEndField.setJavaType("String");
                    timeEndField.setSqlType(fieldInfo.getSqlType());
                    fieldExtendList.add(timeEndField);
                }
            }
            tableInfo.setHaveDateTime(haveDateTime);
            tableInfo.setHaveDate(haveDate);
            tableInfo.setHaveBigDecimal(haveBigDecimal);
            tableInfo.setFieldList(fieldInfoList);
            tableInfo.setFieldExtendList(fieldExtendList);
        } catch (Exception e) {
            logger.error("读取字段失败", e);
        } finally {
            if (fieldResult != null) {
                try {
                    fieldResult.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // 读取表索引信息
    private static void getKeyIndexInfo(TableInfo tableInfo) {
        PreparedStatement ps = null;
        ResultSet fieldResult = null;

        Map<String, FieldInfo> tempMap = new HashMap();
        for (FieldInfo fieldInfo : tableInfo.getFieldList()) {
            tempMap.put(fieldInfo.getFieldName(), fieldInfo);
        }

        try {
            ps = conn.prepareStatement(String.format(SQL_SHOW_TABLE_INDEX, tableInfo.getTableName()));
            fieldResult = ps.executeQuery();
            while (fieldResult.next()) {
                Integer nonUnique = fieldResult.getInt("Non_unique");
                String keyName = fieldResult.getString("Key_name");
                String ColumnName = fieldResult.getString("Column_name");

                if (nonUnique == 1) {
                    continue;
                }

                List<FieldInfo> keyFieldList = tableInfo.getKeyIndexMap().get(keyName);
                if (keyFieldList == null) {
                    keyFieldList = new ArrayList();
                    tableInfo.getKeyIndexMap().put(keyName, keyFieldList);
                }
                keyFieldList.add(tempMap.get(ColumnName));
            }
        } catch (Exception e) {
            logger.error("读取索引失败", e);
        } finally {
            if (fieldResult != null) {
                try {
                    fieldResult.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // 处理字段名，将下划线分隔的字段名转换为驼峰式命名
    // 如：user_name -> userName
    private static String processField(String field, Boolean upperCaseFirstLetter) {
        StringBuffer sb = new StringBuffer();
        String[] fields = field.split("_");
        sb.append(upperCaseFirstLetter ? StringUtils.upperCaseFirstLetter(fields[0]) : fields[0]);
        for (int i = 1, len = fields.length; i < len; i++) {
            sb.append(StringUtils.upperCaseFirstLetter(fields[i]));
        }
        return sb.toString();
    }

    //sqlType->javaType
    private static String processJavaType(String sqlType) {
        if (ArrayUtils.contains(Constants.SQL_INTEGER_TYPES, sqlType)) {
            return "Integer";
        } else if (ArrayUtils.contains(Constants.SQL_LONG_TYPES, sqlType)) {
            return "Long";
        } else if (ArrayUtils.contains(Constants.SQL_STRING_TYPES, sqlType)) {
            return "String";
        } else if (ArrayUtils.contains(Constants.SQL_DATE_TIME_TYPES, sqlType) || ArrayUtils.contains(Constants.SQL_DATE_TYPES, sqlType)) {
            return "Date";
        } else if (ArrayUtils.contains(Constants.SQL_DECIMAL_TYPES, sqlType)) {
            return "BigDecimal";
        } else {
            throw new RuntimeException("无法识别的类型：" + sqlType);
        }
    }
}
